﻿using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;


namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 根據用戶名和模塊代號取得用戶模塊權限
        /// </summary>
        /// <param name="Account">用戶名</param>
        /// <param name="ModuleCode">模塊代號</param>
        /// <returns></returns>
        public DataSet GetModulePermissions(string Account, int ModuleCode)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,50,Account),
                                     DbHelper.MakeInParam("@moduleCode",(DbType)SqlDbType.TinyInt,4,ModuleCode),
                                 };
            string commandText = "SELECT DISTINCT MAX([dbo].[XS_A_Permissions].[Read])AS [READ], MAX([dbo].[XS_A_Permissions].[Edit])AS [EDIT],";
            commandText += " MAX([dbo].[XS_A_Permissions].[Delete])AS [DELETE], MAX([dbo].[XS_A_Permissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[XS_A_UserRoles] INNER JOIN [dbo].[XS_A_User] ON ";
            commandText+="[dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID] INNER JOIN [dbo].[XS_A_Roles] ON ";
            commandText+="[dbo].[XS_A_UserRoles].[RolesId] = [dbo].[XS_A_Roles].[ID] INNER JOIN [dbo].[XS_A_Module] INNER JOIN";
            commandText+="[dbo].[XS_A_RolesModule] ON [dbo].[XS_A_Module].[ID] = dbo.XS_A_RolesModule.ModuleID ON";
            commandText+="[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText+="[dbo].[XS_A_Permissions] INNER JOIN [dbo].[XS_A_RolesPermissions] ON ";
            commandText+="[dbo].[XS_A_Permissions].[ID] = [dbo].[XS_A_RolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesPermissions].[RolesId]";
            commandText += "WHERE  ([dbo].[XS_A_User].[Account] =@account AND  [dbo].[XS_A_Module].[KeyCode]=@moduleCode);";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名和模塊名稱取得用戶模塊權限
        /// </summary>
        /// <param name="Account">用戶名</param>
        /// <param name="ModuleName">模塊名</param>
        /// <returns></returns>
        public DataSet GetModulePermissions(string Account, string ModuleName)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,50,Account),
                                     DbHelper.MakeInParam("@modulename",(DbType)SqlDbType.NVarChar,50,ModuleName),
                                 };

            string commandText = "SELECT DISTINCT MAX([dbo].[XS_A_Permissions].[Read])AS [READ], MAX([dbo].[XS_A_Permissions].[Edit])AS [EDIT],";
            commandText += " MAX([dbo].[XS_A_Permissions].[Delete])AS [DELETE], MAX([dbo].[XS_A_Permissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[XS_A_Module] INNER JOIN";
            commandText += "[dbo].[XS_Menu] ON [dbo].[XS_A_Module].[ID] = [dbo].[XS_Menu].[ModuleID] INNER JOIN";
            commandText += "[dbo].[XS_A_Permissions] INNER JOIN";
            commandText += "[dbo].[XS_A_RolesPermissions] ON [dbo].[XS_A_Permissions].[ID] = [dbo].[XS_A_RolesPermissions].[PermissionsId] INNER JOIN";
            commandText += "[dbo].[XS_A_Roles] ON [dbo].[XS_A_RolesPermissions].[RolesId] = [dbo].[XS_A_Roles].[ID] INNER JOIN";
            commandText += "[dbo].[XS_A_UserRoles] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_UserRoles].[RolesId] INNER JOIN";
            commandText += "[dbo].[XS_A_User] ON [dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID] INNER JOIN";
            commandText += "[dbo].[XS_A_RolesModule] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] ON ";
            commandText += "[dbo].[XS_A_Module].[ID] = [dbo].[XS_A_RolesModule].[ModuleID]";
            commandText += "WHERE  ([dbo].[XS_A_User].[Account] =@account AND  [dbo].[XS_A_Module].[name]=@modulename);";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據帳號取得所有模塊角色權限
        /// </summary>
        /// <param name="Account">帳號</param>
        /// <returns></returns>
        public DataSet GetAllModulePermissions(string Account)
        {
            DbParameter getPar =DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,50,Account);

            string commandText = "SELECT DISTINCT [dbo].[XS_A_Module].[KeyCode],[dbo].[XS_A_Module].[Name],";
            commandText += "MAX([dbo].[XS_A_Permissions].[Read])AS [READ], MAX([dbo].[XS_A_Permissions].[Edit])AS [EDIT],";
            commandText += "MAX([dbo].[XS_A_Permissions].[Delete])AS [DELETE], MAX([dbo].[XS_A_Permissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[XS_A_UserRoles] INNER JOIN [dbo].[XS_A_User] ON ";
            commandText += "[dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID] INNER JOIN [dbo].[XS_A_Roles] ON ";
            commandText += "[dbo].[XS_A_UserRoles].[RolesId] = [dbo].[XS_A_Roles].[ID] INNER JOIN [dbo].[XS_A_Module] INNER JOIN";
            commandText += "[dbo].[XS_A_RolesModule] ON [dbo].[XS_A_Module].[ID] = dbo.XS_A_RolesModule.ModuleID ON";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[XS_A_Permissions] INNER JOIN [dbo].[XS_A_RolesPermissions] ON ";
            commandText += "[dbo].[XS_A_Permissions].[ID] = [dbo].[XS_A_RolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesPermissions].[RolesId]";
            commandText += "WHERE  ([dbo].[XS_A_User].[Account] =@account) GROUP BY [dbo].[XS_A_Module].[KeyCode],[dbo].[XS_A_Module].[Name];";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名取得該用戶隸屬角色
        /// </summary>
        /// <param name="Account"></param>
        /// <returns></returns>
        public DataSet GetAllUserRoles(string Account)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 50, Account);

            string commandText = null;
            commandText += "SELECT     [dbo].[XS_A_UserRoles].[id],[dbo].[XS_A_User].[Account], [dbo].[XS_A_Roles].[RolesName],[dbo].[XS_A_UserRoles].[UserId],[dbo].[XS_A_UserRoles].[RolesId]";
            commandText += "FROM       [dbo].[XS_A_Roles] INNER JOIN";
            commandText += "[dbo].[XS_A_UserRoles] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_UserRoles].[RolesId] INNER JOIN";
            commandText += "[dbo].[XS_A_User] ON [dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID]";
            commandText += "WHERE     ([dbo].[XS_A_User].[Account] = @account)";
            //commandText += "GROUP BY [dbo].[XS_A_User].[Account], [dbo].[XS_A_Roles].[RolesName],[dbo].[XS_A_UserRoles].[UserId],[dbo].[XS_A_UserRoles].[RolesId]";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);

        }
        /// <summary>
        /// 根據用戶名取得該用戶擁有操作模塊資源
        /// </summary>
        /// <param name="Account"></param>
        /// <returns></returns>
        public DataSet GetAllUserModule(string Account)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 50, Account);

            string commandText = null;
            commandText += "SELECT     [dbo].[XS_A_User].[Account], [dbo].[XS_A_Module].[Name], [dbo].[XS_A_Module].[KeyCode]";
            commandText += "FROM         [dbo].[XS_A_UserRoles] INNER JOIN";
            commandText += "[dbo].[XS_A_User] ON [dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID] INNER JOIN";
            commandText += "[dbo].[XS_A_Roles] ON [dbo].[XS_A_UserRoles].[RolesId] = [dbo].[XS_A_Roles].[ID] INNER JOIN";
            commandText += "[dbo].[XS_A_Module] INNER JOIN";
            commandText += "[dbo].[XS_A_RolesModule] ON [dbo].[XS_A_Module].[ID] = [dbo].[XS_A_RolesModule].[ModuleID] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID]";
            commandText += "WHERE     (dbo.XS_A_User.Account = 'admin')";
            commandText += "GROUP BY [dbo].[XS_A_User].[Account], [dbo].[XS_A_Module].[Name], [dbo].[XS_A_Module].[KeyCode]";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得所有模塊資源
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllModule()
        {

            string commandText = "SELECT * FROM [dbo].[XS_A_Module]";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }           
        /// <summary>
        /// 創建一個新角色
        /// </summary>
        /// <param name="RolesName">角色名</param>
        /// <returns></returns>
        public int AddNewRoles(string RolesName)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);

            string commandText = "INSERT INTO [dbo].[XS_A_Roles]([RolesName]) VALUES(@rolesName)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 創建一個新角色并返回角色ID
        /// </summary>
        /// <param name="RolesName">角色名</param>
        /// <returns></returns>
        public object AddNewRolesReturnId(string RolesName)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);

            string commandText = "INSERT INTO [dbo].[XS_A_Roles]([RolesName]) VALUES(@rolesName);SELECT @@IDENTITY";

            return DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色名刪除角色
        /// </summary>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public int DeleteRoles(string RolesName)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);
            string commandText = "DELETE FROM [dbo].[XS_A_Roles] WHERE [dbo].[XS_A_Roles].[name]=@rolesname";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色主鍵ID刪除角色
        /// </summary>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteRoles(int RolesId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesid", (DbType)SqlDbType.Int, int.MaxValue, RolesId);
            string commandText = "DELETE FROM [dbo].[XS_A_Roles] WHERE [dbo].[XS_A_Roles].[name]=@rolesid";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ROLES表組件ID更改角色名
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public int UpdateRolesName(int RolesId, string RolesName)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DbHelper.MakeInParam("@name",(DbType)SqlDbType.NVarChar,50,RolesName),
                                 };
            string commandText = "UPDATE [dbo].[XS_A_Roles] SET [name]=@name WHERE [ID]=@rolesid";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int AddNewUserRoles(int UserId, int RolesId)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "INSERT INTO [dbo].[XS_A_UserRoles]([UserId],[RolesId])VALUES(@userid,@rolesid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int AddNewUserRoles(string UserId, string RolesId)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "INSERT INTO [dbo].[XS_A_UserRoles]([UserId],[RolesId])VALUES(@userid,@rolesid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據UserRoles主鍵ID刪除用戶角色
        /// </summary>
        /// <param name="UserRolesId"></param>
        /// <returns></returns>
        public int DeleteUserRoles(int UserRolesId)
        {
            DbParameter getPar =DbHelper.MakeInParam("@userrolesid",(DbType)SqlDbType.Int,int.MaxValue,UserRolesId);
            string commandText = "DELETE FROM [dbo].[XS_A_UserRoles] WHERE ([id]=@userrolesid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 刪除用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteUserRoles(int UserId, int RolesId)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "DELETE FROM [dbo].[XS_A_UserRoles] WHERE ([userid]=@userid AND [rolesid]=@rolesid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據指定用戶ID刪除用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteUserRoles(string UserId)
        {
            DbParameter getPar =DbHelper.MakeInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId);
            string commandText = "DELETE FROM [dbo].[XS_A_UserRoles] WHERE ([userid]=@userid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名取得用戶主鍵ID
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public object GetUserID(string UserName)
        {
            DbParameter getPar =DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,50,UserName);
            string commandText = "SELECT [ID] FROM [dbo].[XS_A_User] WHERE([account]=@account)";
            return DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色名取得角色主鍵ID
        /// </summary>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public object GetRolesID(string RolesName)
        {
            DbParameter getPar = DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, RolesName);
            string commandText = "SELECT [ID] FROM [dbo].[XS_A_Roles] WHERE([rolesname]=@name)";
            return DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得角色表所有信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetRolesInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_A_Roles]";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得角色模塊資源
        /// </summary>
        /// <param name="rolesid"></param>
        /// <returns></returns>
        public DataSet GetRolesModule(string rolesid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,@rolesid);
            string commandText = "";
            commandText += "SELECT [dbo].[XS_A_Roles].[RolesName], [dbo].[XS_A_Module].[Name] AS [ModuleName],";
            commandText += "[dbo].[XS_A_Roles].[ID] AS [RolesId], [dbo].[XS_A_Module].[KeyCode],";
            commandText += "[dbo].[XS_A_RolesModule].[ModuleID],[dbo].[XS_A_RolesModule].[ID] AS [RolesModuleId]";
            commandText += "FROM [dbo].[XS_A_Roles] INNER JOIN [dbo].[XS_A_RolesModule] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[XS_A_Module] ON [dbo].[XS_A_RolesModule].[ModuleID] = [dbo].[XS_A_Module].[ID]";
            commandText += "WHERE [dbo].[XS_A_Roles].[ID]=@id";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得權限類表全部記錄信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetPermissionsInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_A_Permissions]";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText);
        }
        /// <summary>
        /// 添加一組操作權限
        /// </summary>
        /// <param name="Read"></param>
        /// <param name="Edit"></param>
        /// <param name="Delete"></param>
        /// <param name="Add"></param>
        /// <returns></returns>
        public int AddNewPermissions(PermissionsInfo Per)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@read",(DbType)SqlDbType.Int,int.MaxValue,Per.Read),
                                     DbHelper.MakeInParam("@edit",(DbType)SqlDbType.Int,int.MaxValue,Per.Edit),
                                     DbHelper.MakeInParam("@delete",(DbType)SqlDbType.Int,int.MaxValue,Per.Delete),
                                     DbHelper.MakeInParam("@add",(DbType)SqlDbType.Int,int.MaxValue,Per.Add),
                                 };
            string commandText = "INSERT INTO [dbo].[XS_A_Permissions]([Read],[Edit],[Delete],[Add])VALUES(@read,@edit,@delete,@add)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據權限主鍵ID刪除權限組
        /// </summary>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int DeletePermissions(int PermissionsId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@permissionsid", (DbType)SqlDbType.Int, int.MaxValue, PermissionsId);
            string commandText = "DELETE FROM [dbo].[XS_A_Permissions] WHERE ([id]=@permissionsid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）角色權限
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int AddNewRolesPermissions(int RolesId, int PermissionsId)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DbHelper.MakeInParam("@permissionsid",(DbType)SqlDbType.Int,int.MaxValue,PermissionsId),
                                 };
            string commandText = "INSERT INTO [dbo].[XS_A_RolesPermissions]([RolesId],[PermissionsId])VALUES(@rolesid,@permissionsid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 刪除角色權限
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int DeleteRolesPermissions(int RolesId, int PermissionsId)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DbHelper.MakeInParam("@permissionsid",(DbType)SqlDbType.Int,int.MaxValue,PermissionsId),
                                 };
            string commandText = "DELETE FROM [dbo].[XS_A_RolesPermissions] WHERE ([rolesid]=@rolesid AND [permissionsid]=@permissionsid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據RolesPermissions主鍵ID刪除角色權限
        /// </summary>
        /// <param name="RolesPermissionsId"></param>
        /// <returns></returns>
        public int DeleteRolesPermissions(int RolesPermissionsId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolespermissionsid", (DbType)SqlDbType.Int, int.MaxValue, RolesPermissionsId);
            string commandText = "DELETE FROM [dbo].[XS_A_Permissions] WHERE ([id]=@rolespermissionsid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加新操作模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int AddNewModule(ModuleInfo MInfo)
        {
            DbParameter getPar = DbHelper.MakeInParam("@modulename", (DbType)SqlDbType.NVarChar, 50, MInfo.Name);
            string commandText = "INSERT INTO [dbo].[XS_A_Module]([Name])VALUES(@modulename)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID更新模塊名
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int UpdateModuleName(ModuleInfo MInfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,MInfo.ID),
                                     DbHelper.MakeInParam("@modulename",(DbType)SqlDbType.Int,int.MaxValue,MInfo.Name),
                                 };
            string commandText = "UPDATE [dbo].[XS_A_Module] SET [name]=@modulename WHERE [ID]=@moduleid";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID刪除模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int DeleteModuleById(ModuleInfo MInfo)
        {
            DbParameter getPar = DbHelper.MakeInParam("@moduleid", (DbType)SqlDbType.NVarChar, 50, MInfo.ID);
            string commandText = "DELETE FROM [dbo].[XS_A_Module] WHERE ([id]=@moduleid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊名刪除模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int DeleteModuleByName(ModuleInfo MInfo)
        {
            DbParameter getPar = DbHelper.MakeInParam("@modulename", (DbType)SqlDbType.NVarChar, 50, MInfo.Name);
            string commandText = "DELETE FROM [dbo].[XS_A_Module] WHERE ([name]=@modulename)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 給角色指定（添加）操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int AddNewRolesModule(RolesModuleInfo RMinfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.ModuleID),
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.RolesID),
                                 };
            string commandText = "INSERT INTO [dbo].[XS_A_RolesModule]([RolesId],[ModuleId])VALUES(@rolesid,@moduleid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據主鍵ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteRolesModuleById(RolesModuleInfo RMinfo)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesmoduleid", (DbType)SqlDbType.NVarChar, 50, RMinfo.ID);
            string commandText = "DELETE FROM [dbo].[XS_A_RolesModule] WHERE ([id]=@rolesmoduleid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteRolesModuleByRolesId(RolesModuleInfo RMinfo)
        {
            DbParameter getPar = DbHelper.MakeInParam("@rolesid", (DbType)SqlDbType.Int,int.MaxValue, RMinfo.RolesID);
            string commandText = "DELETE FROM [dbo].[XS_A_RolesModule] WHERE ([rolesid]=@rolesid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID和角色ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteRolesModuleByRolesIdAndModuleId(RolesModuleInfo RMinfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.ModuleID),
                                     DbHelper.MakeInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.RolesID),
                                 };
            string commandText = "DELETE FROM [dbo].[XS_A_RolesModule] WHERE ([rolesid]=@rolesid AND [moduleid]=@moduleid)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 取得所有模塊與角色與權限信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllRolesModulePermissions()
        {
            string commandText = "SELECT ";
            commandText += "[dbo].[XS_A_Roles].[RolesName], [dbo].[XS_A_Roles].[ID] AS [RolesId], ";
            commandText += "[dbo].[XS_A_Module].[Name] AS [ModuleName], [dbo].[XS_A_Module].[KeyCode], ";
            commandText += "MAX([dbo].[XS_A_Permissions].[Read]) AS [Read],MAX([dbo].[XS_A_Permissions].[Edit]) AS [Edit], ";
            commandText += "MAX([dbo].[XS_A_Permissions].[Delete]) AS [Delete],MAX([dbo].[XS_A_Permissions].[Add]) AS [Add]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_A_Module] INNER JOIN [dbo].[XS_A_Permissions] INNER JOIN";
            commandText += "[dbo].[XS_A_Roles] INNER JOIN [dbo].[XS_A_RolesModule] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[XS_A_RolesPermissions] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesPermissions].[RolesId] ON";
            commandText += "[dbo].[XS_A_Permissions].[ID] = [dbo].[XS_A_RolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[XS_A_Module].[ID] = [dbo].[XS_A_RolesModule].[ModuleID]";
            commandText += "GROUP BY ";
            commandText += "[dbo].[XS_A_Roles].[RolesName], [dbo].[XS_A_Roles].[ID],";
            commandText += "[dbo].[XS_A_Module].[Name],[dbo].[XS_A_Module].[KeyCode]";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText);
        }
        /// <summary>
        /// 取得所有角色信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllRolesInfo()
        {
            string commandText = "Select *  FROM [dbo].[XS_A_Roles] ";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據ID刪除角色和所有關聯表數據
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelRolesAndInnerTable(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);


            string commandText = "DELETE  FROM [dbo].[XS_A_RolesModule] WHERE [rolesid]=@id";
            int i = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[XS_A_RolesPermissions] WHERE [rolesid]=@id";
            i = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[XS_A_UserRoles] WHERE [rolesid]=@id";
            i = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[XS_A_Roles] WHERE [id]=@id";
            i = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            return i;
        }
    }
}
